use chrono::{DateTime, Utc};
use indoc::indoc;
use rusqlite::{Connection, Row, named_params, params};

use super::{PodcastDBId, convert_date};
use crate::podcast::PodcastNoId;

/// A struct representing a podcast feed in the database
#[derive(Debug, Clone)]
pub struct PodcastDB {
    pub id: PodcastDBId,
    pub title: String,
    pub url: String,
    pub description: Option<String>,
    pub author: Option<String>,
    pub explicit: Option<bool>,
    pub last_checked: DateTime<Utc>,
    pub image_url: Option<String>,
}

impl PodcastDB {
    /// Try to convert a given row to a [`PodcastDB`] instance, using column names to resolve the values
    pub fn try_from_row_named(row: &Row<'_>) -> Result<Self, rusqlite::Error> {
        // NOTE: all the names in "get" below are the *column names* as defined in migrations/001.sql#table_podcasts (pseudo link)
        let last_checked =
            convert_date(&row.get("last_checked")).ok_or(rusqlite::Error::InvalidQuery)?;
        Ok(PodcastDB {
            id: row.get("id")?,
            title: row.get("title")?,
            url: row.get("url")?,
            description: row.get("description")?,
            author: row.get("author")?,
            explicit: row.get("explicit")?,
            last_checked,
            image_url: row.get("image_url")?,
        })
    }
}

/// A struct representing a podcast feed in the database to be inserted
///
/// This is required as some fields are auto-generated by the database compared to [`PodcastDB`]
#[derive(Debug, Clone)]
pub struct PodcastDBInsertable<'a> {
    // generated by the database
    // pub id: PodcastDBId,
    pub title: &'a str,
    pub url: &'a str,
    pub description: Option<&'a str>,
    pub author: Option<&'a str>,
    pub explicit: Option<bool>,
    pub last_checked: DateTime<Utc>,
    pub image_url: Option<&'a str>,
}

impl<'a> From<&'a PodcastNoId> for PodcastDBInsertable<'a> {
    fn from(value: &'a PodcastNoId) -> Self {
        Self {
            title: &value.title,
            url: &value.url,
            description: value.description.as_deref(),
            author: value.author.as_deref(),
            explicit: value.explicit,
            last_checked: value.last_checked,
            image_url: value.image_url.as_deref(),
        }
    }
}

impl PodcastDBInsertable<'_> {
    /// Insert the current [`PodcastDBInsertable`] into the `podcasts` table
    #[inline]
    pub fn insert_podcast(&self, con: &Connection) -> Result<usize, rusqlite::Error> {
        let mut stmt = con.prepare_cached(indoc! {"
            INSERT INTO podcasts (title, url, description, author, explicit, last_checked, image_url)
            VALUES (:title, :url, :description, :author, :explicit, :last_checked, :image_url);
        "})?;
        stmt.execute(named_params![
            ":title": self.title,
            ":url": self.url,
            ":description": self.description,
            ":author": self.author,
            ":explicit": self.explicit,
            ":last_checked": self.last_checked.timestamp(),
            ":image_url": self.image_url
        ])
    }

    /// Update a given id with the current [`PodcastDBInsertable`] in the `podcasts` table
    #[inline]
    pub fn update_podcast(
        &self,
        id: PodcastDBId,
        con: &Connection,
    ) -> Result<usize, rusqlite::Error> {
        let mut stmt = con.prepare_cached(indoc! {"
            UPDATE podcasts SET title = :title, url = :url, description = :description,
                author = :author, explicit = :explicit, last_checked = :last_checked
            WHERE id = :id;
        "})?;
        stmt.execute(named_params![
            ":title": self.title,
            ":url": self.url,
            ":description": self.description,
            ":author": self.author,
            ":explicit": self.explicit,
            ":last_checked": self.last_checked.timestamp(),
            ":id": id,
        ])
    }
}

/// Delete a podcast by id
///
/// This also deletes all associated episodes and files (not removing the actual files)!
pub fn delete_podcast(id: PodcastDBId, con: &Connection) -> Result<usize, rusqlite::Error> {
    // Note: Because of the foreign key constraints on `episodes`
    // and `files` tables, all associated episodes for this podcast
    // will also be deleted, and all associated file entries for
    // those episodes as well.
    let mut stmt = con.prepare_cached("DELETE FROM podcasts WHERE id = ?;")?;
    stmt.execute(params![id])
}
